ADD JAR hdfs:///hive_udf/ojdbc8-19.3.0.0.jar;

CREATE TEMPORARY VIEW dm_pre_rate_agent_summary_dt_view
USING org.apache.spark.sql.jdbc
OPTIONS (
url "jdbc:oracle:thin:@//pro-bigdatareport-2023-readwrite-ora.yl.com:1521/orcl",
dbtable 'DM_PRE_RATE_AGENT_SUMMARY_DT',
driver 'oracle.jdbc.driver.OracleDriver',
user 'jms_bigdata_report',
password 'H15DCte03YPW3B2pEA',
isolationLevel 'NONE', -- 事务级别
batchsize 1000, -- 默认1000
truncate true -- 是否清空数据
);


INSERT overwrite TABLE dm_pre_rate_agent_summary_dt_view
select date_time                            ,
       data_type                            ,
       island_code                          ,
       island_name                          ,
       start_subordinate_agent_code         ,
       start_subordinate_agent_name         ,
       sign_subordinate_agent_code          ,
       sign_subordinate_agent_name          ,
       start_subordinate_virt_code          ,
       start_subordinate_virt_name          ,
       sign_subordinate_virt_code           ,
       sign_subordinate_virt_name           ,
       plan_pdd_date                        ,
       plan_mountain_date                   ,
       plan_free_date                       ,
       plan_jd_date                         ,
       plan_cainiao_date                    ,
       plan_cainiao_pro_date                ,
       plan_mountain_pro_date               ,
       is_districtprescription              ,
       real_reach_sum                       ,
       real_total                           ,
       pres_reach_sum                       ,
       pres_total                           ,
       is_platform_effect                   ,
       real_platform_reach_sum              ,
       real_platform_total                  ,
       pres_platform_reach_sum              ,
       pres_platform_total                  ,
       is_mountain_platform_effect          ,
       mountain_real_platform_reach_sum     ,
       mountain_real_platform_total         ,
       mountain_pres_platform_reach_sum     ,
       mountain_pres_platform_total         ,
       is_free_platform_effect              ,
       free_real_platform_reach_sum         ,
       free_real_platform_total             ,
       free_pres_platform_reach_sum         ,
       free_pres_platform_total             ,
       is_jd_platform_effect                ,
       jd_real_platform_reach_sum           ,
       jd_real_platform_total               ,
       jd_pres_platform_reach_sum           ,
       jd_pres_platform_total               ,
       is_cainiao_platform_effect           ,
       cainiao_real_platform_reach_sum      ,
       cainiao_real_platform_total          ,
       cainiao_pres_platform_reach_sum      ,
       cainiao_pres_platform_total          ,
       is_cainiao_pro_platform_effect       ,
       cainiao_pro_real_platform_reach_sum  ,
       cainiao_pro_real_platform_total      ,
       cainiao_pro_pres_platform_reach_sum  ,
       cainiao_pro_pres_platform_total      ,
       is_mountain_pro_platform_effect      ,
       mountain_pro_real_platform_reach_sum ,
       mountain_pro_real_platform_total     ,
       mountain_pro_pres_platform_reach_sum ,
       mountain_pro_pres_platform_total     ,
       real_zeroday_num                     ,
       real_oneday_num                      ,
       real_twoday_num                      ,
       real_threeday_num                    ,
       real_four_num                        ,
       real_five_num                        ,
       real_six_num                         ,
       pres_zeroday_num                     ,
       pres_oneday_num                      ,
       pres_twoday_num                      ,
       pres_threeday_num                    ,
       pres_four_num                        ,
       pres_five_num                        ,
       pres_six_num                         ,
       jd_real_one_num                      ,
       jd_real_two_num                      ,
       jd_real_three_num                    ,
       jd_real_four_num                     ,
       jd_real_five_num                     ,
       jd_real_six_num                      ,
       jd_real_seven_num                    ,
       jd_pre_one_num                       ,
       jd_pre_two_num                       ,
       jd_pre_three_num                     ,
       jd_pre_four_num                      ,
       jd_pre_five_num                      ,
       jd_pre_six_num                       ,
       jd_pre_seven_num                     ,
       from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') as update_time
from jms_dm.dm_pre_rate_agent_summary_dt
where dt >= date_sub('{{ execution_date | cst_ds }}',29) and dt <= date_add('{{ execution_date | cst_ds }}',3)
;